package com.roc.web.action.merinfo;

import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;
import com.roc.util.constant.Role;

public class BindMerinfoSearchCondtion extends BaseSearchCondition{

	
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private String roleId; //角色 
	private String merchantNo;//商户号
	private String merchantName;//商户简称
	private String opId;//操作员id
	private String  minDate;//入网时间
	private String  maxDate;//入网时间
	private String groupCd; //受理市场标识
	private String sn;//终端号
	@Override
	protected String getOrderPartOfHQL() {
		return " ORDER BY A.REC_CRT_TS DESC  ";
	}

	@Override
	protected String getRowCountHQL() {
		StringBuffer  sb= new StringBuffer();
		
		sb.append(" SELECT count(1)                                       \n ");
		sb.append("   FROM TBL_MCHT_INF A,                                               \n ");
		sb.append("        TBL_MCHT_BANKACCOUNT D,                                       \n ");
		sb.append("        (SELECT T1.INS_ID_CD, T1.INS_NAME FROM TBL_INS_INF T1) TI,    \n ");
		sb.append("        (SELECT T.ID,                                                 \n ");
		sb.append("                T.REAL_NAME,                                          \n ");
		sb.append("                (SELECT ac.name                                  \n ");
		sb.append("                   FROM tbl_area ac                                \n ");
		sb.append("                  WHERE ac.id=t.companyid) COMPANYNAME,  \n ");
		sb.append("                (SELECT TT.REAL_NAME                                  \n ");
		sb.append("                   FROM T_OPERATORS TT                                \n ");
		sb.append("                  WHERE TT.ID = TO_NUMBER(T.ONEAGENTID)) ONEAGENGNAME \n ");
		sb.append("           FROM T_OPERATORS T) OP,TBL_TERM_INF TE                                     \n ");
		sb.append("  WHERE A.MCHT_CD = D.OWNER_CD                                        \n ");
		sb.append("    AND  OP.ID = TO_NUMBER(A.EXT1)                                     \n ");
		sb.append("    AND A.BANK_BELONG_CD = TI.INS_ID_CD AND A.MCHT_CD=TE.MCHT_CD AND A.STATUS='01' AND A.group_cd='2'    \n ");
		
        if(roleId.equals(Role.O_MANAGER.getCode())){//机构管理员
            sb.append("  and exists (select 1   from TBL_MER_AGENT_RELATION tmar where TMAR.MERNO = A.MCHT_CD   and tmar.agentid =").append(opId).append(" )");
  		}
        
		return sb.toString();
	}

	 
	@Override
	protected String getSearchHQL() {
		StringBuffer  sb= new StringBuffer();
	
		sb.append(" SELECT A.MCHT_CD,                                           \n ");
		sb.append("        A.NAME_BUSI,                                                  \n ");
		sb.append("        A.NAME MERNAME,                                               \n ");
		sb.append("        TI.INS_NAME,                                                  \n ");
		sb.append("        A.CONTACT_NAME,                                               \n ");
		sb.append("        A.CONTACT_PHONENO,                                            \n ");
		sb.append("        A.CERTIF,                                                     \n ");
		sb.append("        A.CERTIF_NO,                                                  \n ");
		sb.append("        A.BUSI_LICE_NO,                                               \n ");
		sb.append("        A.REG_ADDR,                                                   \n ");
		sb.append("        A.STATUS,                                                     \n ");
		sb.append("        (SELECT TP.REAL_NAME                                          \n ");
		sb.append("           FROM T_OPERATORS TP                                        \n ");
		sb.append("          WHERE TP.ID= to_number(A.ORI_CHNL_DESC)) RKNAME,           \n ");
		sb.append("        OP.COMPANYNAME,                                               \n ");
		sb.append("        OP.ONEAGENGNAME,                                              \n ");
		sb.append("        OP.REAL_NAME,                                                 \n ");
		sb.append("        D.NAME ACCOUNTNAME,                                           \n ");
		sb.append("        D.ACCOUNT,                                                    \n ");
		sb.append("        D.BANK_NAME,                                                  \n ");
		sb.append("        D.BANK_CODE,                                                  \n ");
		sb.append("        NVL2((SELECT DISTINCT PROD_CD                                 \n ");
		sb.append("               FROM TBL_MCHT_BIZ_DEAL t                               \n ");
		sb.append("              WHERE t.MCHT_CD = A.MCHT_CD                             \n ");
		sb.append("                AND t.PROD_CD = '1000'),                              \n ");
		sb.append("             'T1',                                                    \n ");
		sb.append("             ''),                                                     \n ");
		sb.append("        NVL2((SELECT DISTINCT PROD_CD                                 \n ");
		sb.append("               FROM TBL_MCHT_BIZ_DEAL t                               \n ");
		sb.append("              WHERE t.MCHT_CD = A.MCHT_CD                             \n ");
		sb.append("                AND t.PROD_CD = '1171'),                              \n ");
		sb.append("             'T0',                                                    \n ");
		sb.append("             ''),                                                     \n ");
		sb.append("        A.GROUP_CD,                                                   \n ");
		sb.append("        A.NATION_CD,                                                  \n ");
		sb.append("        A.ISGROUP,                                                    \n ");
		sb.append("        A.APPL_DATE,                                                   \n ");
		sb.append("        NVL2((SELECT DISTINCT PROD_CD                                 \n ");
		sb.append("               FROM TBL_MCHT_BIZ_DEAL t                               \n ");
		sb.append("              WHERE t.MCHT_CD = A.MCHT_CD                             \n ");
		sb.append("                AND t.PROD_CD = '3000'),                              \n ");
		sb.append("             '直连T1',                                                    \n ");
		sb.append("             ''),                                                     \n ");
		sb.append("        NVL2((SELECT DISTINCT PROD_CD                                 \n ");
		sb.append("               FROM TBL_MCHT_BIZ_DEAL t                               \n ");
		sb.append("              WHERE t.MCHT_CD = A.MCHT_CD                             \n ");
		sb.append("                AND t.PROD_CD = '1271'),                              \n ");
		sb.append("             '直连T0',                                                    \n ");
		sb.append("             '')                                                     \n ");
		
		sb.append("     ,nvl((select decode(ei.term_property,'0','免投','1','押金','2','销售') from tbl_mcht_extend_info ei where A.MCHT_CD=ei.mcth_cd ),'空') term_property, \n ");
		sb.append("     nvl((select ei.certif_start_date||'---'||ei.certif_end_date from tbl_mcht_extend_info ei where A.MCHT_CD=ei.mcth_cd ) ,'空') certif_date, \n ");
		sb.append("     nvl((select ei.busi_lice_start_date||'---'||ei.busi_lice_end_date from tbl_mcht_extend_info ei where A.MCHT_CD=ei.mcth_cd ),'空') busi_lice_date \n ");
		sb.append("   ,nvl((select os.real_name from t_operators os where to_char(os.id)= (select ei.develop_person from tbl_mcht_extend_info ei where A.MCHT_CD=ei.mcth_cd )),'空') develop_person  \n ");
		sb.append(" ,TE.TERM_ID,TE.DEVICE_CD ");
		sb.append("   FROM TBL_MCHT_INF A,                                               \n ");
		sb.append("        TBL_MCHT_BANKACCOUNT D,                                       \n ");
		sb.append("        (SELECT T1.INS_ID_CD, T1.INS_NAME FROM TBL_INS_INF T1) TI,    \n ");
		sb.append("        (SELECT T.ID,                                                 \n ");
		sb.append("                T.REAL_NAME,                                          \n ");
		sb.append("                (SELECT ac.name                                  \n ");
		sb.append("                   FROM tbl_area ac                                \n ");
		sb.append("                  WHERE ac.id=t.companyid) COMPANYNAME,  \n ");
		sb.append("                (SELECT TT.REAL_NAME                                  \n ");
		sb.append("                   FROM T_OPERATORS TT                                \n ");
		sb.append("                  WHERE TT.ID = TO_NUMBER(T.ONEAGENTID)) ONEAGENGNAME \n ");
		sb.append("           FROM T_OPERATORS T) OP,TBL_TERM_INF TE                                     \n ");
		sb.append("  WHERE A.MCHT_CD = D.OWNER_CD                                        \n ");
		sb.append("    AND OP.ID = TO_NUMBER(A.EXT1)                                     \n ");
	    sb.append("    AND A.BANK_BELONG_CD = TI.INS_ID_CD AND A.MCHT_CD=TE.MCHT_CD AND A.STATUS='01' AND A.group_cd='2'                     \n ");
	    
	    if(roleId.equals(Role.O_MANAGER.getCode())){//机构管理员
            sb.append("  and exists (select 1   from TBL_MER_AGENT_RELATION tmar where TMAR.MERNO = A.MCHT_CD   and tmar.agentid =").append(opId).append(" )");
  		}
	    
		return sb.toString();
	}

	 
	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();
		
		if(Constance.isNotEmpty(merchantNo)){ //商户号
			filedBeanList.add(new WhereHQLFieldBean("A.MCHT_CD", merchantNo.trim(), "=", false));
		}
		 
		if(Constance.isNotEmpty(merchantName)){ //商户名称 （模糊）
			filedBeanList.add(new WhereHQLFieldBean("A.NAME_BUSI", merchantName.trim(), "like", false,"ALL"));
		}
		
		//SN
		if(Constance.isNotEmpty(sn)){ //SN
			filedBeanList.add(new WhereHQLFieldBean("TE.DEVICE_CD", sn.trim(), "=", false));
		}
		 
		if(Constance.isNotEmpty(minDate)&&Constance.isNotEmpty(maxDate)){ //入网时间
			filedBeanList.add(new WhereHQLFieldBean("A.APPL_DATE",minDate.trim(),">=", false));
       	    filedBeanList.add(new WhereHQLFieldBean("A.APPL_DATE",maxDate.trim(),"<=", false));
		}
		
		return filedBeanList;
	}


	public String getMerchantNo() {
		return merchantNo;
	}

	public void setMerchantNo(String merchantNo) {
		this.merchantNo = merchantNo;
	}

	public String getMerchantName() {
		return merchantName;
	}

	public void setMerchantName(String merchantName) {
		this.merchantName = merchantName;
	}

	public String getMinDate() {
		return minDate;
	}

	public void setMinDate(String minDate) {
		this.minDate = minDate;
	}

	public String getMaxDate() {
		return maxDate;
	}

	public void setMaxDate(String maxDate) {
		this.maxDate = maxDate;
	}

	public String getRoleId() {
		return roleId;
	}

	public void setRoleId(String roleId) {
		this.roleId = roleId;
	}

	public String getOpId() {
		return opId;
	}

	public void setOpId(String opId) {
		this.opId = opId;
	}

	public String getGroupCd() {
		return groupCd;
	}

	public void setGroupCd(String groupCd) {
		this.groupCd = groupCd;
	}

	public String getSn() {
		return sn;
	}

	public void setSn(String sn) {
		this.sn = sn;
	}

}